﻿Imports VN.INS.DL.DH
Imports VN.INS.BL.BE

Public Class POADA

    Public Function ListaPOA(ByVal strPersona As String, ByVal ano As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try

            lxc_SQL = "SELECT P.NOMBRE, P.ANHIO_FIRMA_POA, P.MONTO_TOTAL_INVERTIDO, P.MONTO_INV_ANHIO,PM.NOMBRES || ' ' || PM.APELLIDO_PATERNO || ' ' || PM.APELLIDO_MATERNO AS MONITOREADOR, PP.PPER, E.EJECUTOR, E.DESCRIPCION AS EJE_DES , " & _
                      "PE.NOMBRES || ' ' || PE.APELLIDO_PATERNO || ' ' || PE.APELLIDO_MATERNO AS ESPECIALISTA, P.OBJETIVO,P.SNIP,P.FECHA_VIABILIDAD, P.POA ,U.NOMBRE AS UBIGEO, TPA.DESCRIPCION,PP. ABREVIATURA, P.ESTADO_REPROG, P.FECHA_REGISTRO FROM POA P " & _
                      "INNER JOIN TIPO_PROYECTO_ACTIVIDAD TPA ON TPA.TIPO_PROYECTO_ACTIVIDAD = P.TIPO_PROYECTO_ACTIVIDAD INNER JOIN PPER PP ON PP.PPER = P.PPER " & _
                      "LEFT JOIN PERSONA PM ON PM.PERSONA = P.PERSONA_MONITOREO " & _
                      "LEFT JOIN PERSONA PE ON PE.PERSONA = P.PERSONA_ESPECIALISTA " & _
                      "LEFT JOIN UBIGEO  U ON U.UBIGEO = P.UBIGEO " & _
                      "LEFT JOIN EJECUTOR E ON P.EJECUTOR = E.EJECUTOR " & _
                      "WHERE 1 = 1 AND P.ANHIO_FIRMA_POA = '" & ano & "'"
            If strPersona <> "" Then
                lxc_SQL = lxc_SQL + " AND '" & strPersona & "' IN (PM.PERSONA, PE.PERSONA) ORDER BY E.DESCRIPCION, P.NOMBRE "
            End If
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaPOA(ByVal strPoa As String, ByVal strPersona As String, ByVal strNombre As String, ByVal strTipo As String, ByVal strAnho As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String

        Try

            lxc_SQL = "SELECT ROWNUM AS NUMERO, P.NOMBRE, P.ANHIO_FIRMA_POA, P.MONTO_TOTAL_INVERTIDO, P.MONTO_INV_ANHIO,PM.NOMBRES || ' ' || PM.APELLIDO_PATERNO || ' ' || PM.APELLIDO_MATERNO AS MONITOREADOR, PP.PPER, E.EJECUTOR, E.DESCRIPCION AS EJE_DES , " & _
                      "PE.NOMBRES || ' ' || PE.APELLIDO_PATERNO || ' ' || PE.APELLIDO_MATERNO AS ESPECIALISTA, P.OBJETIVO,P.SNIP,P.FECHA_VIABILIDAD, P.POA ,U.NOMBRE AS UBIGEO, TPA.DESCRIPCION, PP.ABREVIATURA, P.ESTADO_REPROG, P.FECHA_REGISTRO FROM POA P " & _
                      "INNER JOIN TIPO_PROYECTO_ACTIVIDAD TPA ON TPA.TIPO_PROYECTO_ACTIVIDAD = P.TIPO_PROYECTO_ACTIVIDAD INNER JOIN PPER PP ON PP.PPER = P.PPER " & _
                      "LEFT JOIN PERSONA PM ON PM.PERSONA = P.PERSONA_MONITOREO " & _
                      "LEFT JOIN PERSONA PE ON PE.PERSONA = P.PERSONA_ESPECIALISTA " & _
                      "LEFT JOIN UBIGEO  U ON U.UBIGEO = P.UBIGEO LEFT JOIN EJECUTOR E ON P.EJECUTOR = E.EJECUTOR WHERE 1 = 1 AND P.ANHIO_FIRMA_POA = '" & strAnho & "' "
            If strPersona <> "" Then
                lxc_SQL = lxc_SQL + " AND '" & strPersona & "' IN (PM.PERSONA, PE.PERSONA) "
            End If
            If strNombre <> "" Then
                lxc_SQL = lxc_SQL & "AND UPPER(P.NOMBRE) LIKE UPPER('%" & strNombre & "%') "
            End If
            If strPoa <> "all" Then
                lxc_SQL = lxc_SQL & "AND P.POA LIKE '%" & strPoa & "%' "
            End If
            If strTipo <> "all" Then
                lxc_SQL = lxc_SQL & "AND P.TIPO_PROYECTO_ACTIVIDAD = '" & strTipo & "' "
            End If

            lxc_SQL = lxc_SQL & " ORDER BY E.DESCRIPCION, P.NOMBRE "

            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaAvanceFisico(ByVal strPOA As String, ByVal strPeriodo As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            objDt = New DataTable
            lxc_SQL = " SELECT P.DESCRIPCION, PAF.TAREA_ACTIVIDAD_DESCRIPCION AS Componente, U.DESCRIPCION AS Unidad, PFP.OBSERVACIONES, " & _
            "(NVL(PAF.META_01,0)+ NVL(PAF.META_02,0)+ NVL(PAF.META_03,0)+ NVL(PAF.META_04,0)+ NVL(PAF.META_05,0)+ NVL(PAF.META_06,0) " & _
            "+ NVL(PAF.META_07,0)+ NVL(PAF.META_08,0)+ NVL(PAF.META_09,0)+ NVL(PAF.META_10,0)+ NVL(PAF.META_11,0)+ NVL(PAF.META_12,0)) AS Anual, " & _
            "(CASE WHEN P.MES ='01' THEN PAF.META_01 ELSE " & _
            "(CASE WHEN P.MES ='02' THEN PAF.META_02 ELSE " & _
            "(CASE WHEN P.MES ='03' THEN PAF.META_03 ELSE " & _
            "(CASE WHEN P.MES ='04' THEN PAF.META_04 ELSE " & _
            "(CASE WHEN P.MES ='05' THEN PAF.META_05 ELSE " & _
            "(CASE WHEN P.MES ='06' THEN PAF.META_06 ELSE " & _
            "(CASE WHEN P.MES ='07' THEN PAF.META_07 ELSE " & _
            "(CASE WHEN P.MES ='08' THEN PAF.META_08 ELSE " & _
            "(CASE WHEN P.MES ='09' THEN PAF.META_09 ELSE " & _
            "(CASE WHEN P.MES ='10' THEN PAF.META_10 ELSE " & _
            "(CASE WHEN P.MES ='11' THEN PAF.META_11 ELSE PAF.META_12  END)END)END)END)END)END)END)END)END)END)END) AS  Actual,  " & _
            "(CASE WHEN P.MES <='01' THEN (NVL(PAF.META_01,0)) ELSE 0  END + CASE WHEN P.MES <='02' THEN (NVL(PAF.META_02,0)) ELSE 0  END + " & _
            "CASE WHEN P.MES <='03' THEN (NVL(PAF.META_03,0)) ELSE 0  END + CASE WHEN P.MES <='04' THEN (NVL(PAF.META_04,0)) ELSE 0  END +  " & _
            "CASE WHEN P.MES <='05' THEN (NVL(PAF.META_05,0)) ELSE 0  END + CASE WHEN P.MES <='06' THEN (NVL(PAF.META_06,0)) ELSE 0  END +  " & _
            "CASE WHEN P.MES <='07' THEN (NVL(PAF.META_07,0)) ELSE 0  END + CASE WHEN P.MES <='08' THEN (NVL(PAF.META_08,0)) ELSE 0  END +  " & _
            "CASE WHEN P.MES <='09' THEN (NVL(PAF.META_09,0)) ELSE 0  END + CASE WHEN P.MES <='10' THEN (NVL(PAF.META_10,0)) ELSE 0  END +  " & _
            "CASE WHEN P.MES <='11' THEN (NVL(PAF.META_11,0)) ELSE 0  END + CASE WHEN P.MES <='12' THEN (NVL(PAF.META_12,0)) ELSE 0  END)  AS Acumulada, " & _
            "(CASE WHEN PFP.META_MENSUAL1=0 THEN TO_NUMBER('')  ELSE PFP.META_MENSUAL1  END) AS Visita1, (CASE WHEN PFP.META_MENSUAL2=0 THEN TO_NUMBER('')  ELSE PFP.META_MENSUAL2  END) AS Visita2, " & _
            "(CASE WHEN PFP.META_MENSUAL3=0 THEN TO_NUMBER('')  ELSE PFP.META_MENSUAL3  END) AS Visita3, (CASE WHEN PFP.META_MENSUAL4=0 THEN TO_NUMBER('')  ELSE PFP.META_MENSUAL4  END) AS Visita4 " & _
            "FROM POA_ACTIVIDAD_FISICA PAF  " & _
            "INNER JOIN POA_ACTIVIDAD_COMPONENTE PAC ON PAC.POA = PAF.POA AND PAC.COMPONENTE = PAF.COMPONENTE " & _
            "INNER JOIN UNIDAD U ON U.UNIDAD = PAF.UNIDAD " & _
            "INNER JOIN POA_FISICO_PERSONA PFP ON PFP.ID_POA_ACTIVIDAD_FISICA = PAF.ID_POA_ACTIVIDAD_FISICA  " & _
            "INNER JOIN PLAN_TRABAJO_POA PTO ON PTO.ID_PLAN_TRABAJO_POA = PFP.ID_PLAN_TRABAJO_POA  " & _
            "INNER JOIN PLAN_TRABAJO_PERSONA PTE ON PTE.ID_PLAN_TRABAJO_PERSONA = PTO.ID_PLAN_TRABAJO_PERSONA  " & _
            "INNER JOIN PERIODO P ON P.PERIODO = PTE.PERIODO  " & _
            "where PAF.POA =  '" & strPOA & "' "
            If strPeriodo <> "all" Then
                lxc_SQL = lxc_SQL & "AND P.PERIODO = '" & strPeriodo & "'"
            End If
            lxc_SQL = lxc_SQL + " ORDER BY PFP.ID_POA_ACTIVIDAD_FISICA "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaAvanceFinanciero(ByVal strPOA As String, ByVal strPeriodo As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            objDt = New DataTable
            lxc_SQL = " SELECT P.DESCRIPCION AS PERIODO, EG.ESPECIFICA_GASTO AS CODIGO, GG.DESCRIPCION, PFP.OBSERVACIONES, " & _
            "(NVL(PAF.META_01,0)+ NVL(PAF.META_02,0)+ NVL(PAF.META_03,0)+ NVL(PAF.META_04,0)+ " & _
            "NVL(PAF.META_05,0)+ NVL(PAF.META_06,0) + NVL(PAF.META_07,0)+ NVL(PAF.META_08,0)+ " & _
            "NVL(PAF.META_09,0)+ NVL(PAF.META_10,0)+ NVL(PAF.META_11,0)+ NVL(PAF.META_12,0)) AS Anual," & _
            "(CASE WHEN P.MES = '01' THEN PAF.META_01 ELSE (CASE WHEN P.MES ='02' THEN PAF.META_02 ELSE " & _
            "(CASE WHEN P.MES ='03' THEN PAF.META_03 ELSE (CASE WHEN P.MES ='04' THEN PAF.META_04 ELSE " & _
            "(CASE WHEN P.MES ='05' THEN PAF.META_05 ELSE (CASE WHEN P.MES ='06' THEN PAF.META_06 ELSE " & _
            "(CASE WHEN P.MES ='07' THEN PAF.META_07 ELSE (CASE WHEN P.MES ='08' THEN PAF.META_08 ELSE " & _
            "(CASE WHEN P.MES ='09' THEN PAF.META_09 ELSE (CASE WHEN P.MES ='10' THEN PAF.META_10 ELSE " & _
            "(CASE WHEN P.MES ='11' THEN PAF.META_11 ELSE PAF.META_12  END)END)END)END)END)END)END)END)END)END)END) AS  Actual," & _
            "(CASE WHEN P.MES >='01' THEN (NVL(PAF.META_01,0)) ELSE 0  END) + (CASE WHEN P.MES >='02' THEN (NVL(PAF.META_02,0)) ELSE 0  END) + " & _
            "(CASE WHEN P.MES >='03' THEN (NVL(PAF.META_03,0)) ELSE 0  END) +  (CASE WHEN P.MES >='04' THEN (NVL(PAF.META_04,0)) ELSE 0  END) + " & _
            "(CASE WHEN P.MES >='05' THEN (NVL(PAF.META_05,0)) ELSE 0  END) + (CASE WHEN P.MES >='06' THEN (NVL(PAF.META_06,0)) ELSE 0  END) + " & _
            "(CASE WHEN P.MES >='07' THEN (NVL(PAF.META_07,0)) ELSE 0  END) + (CASE WHEN P.MES >='08' THEN (NVL(PAF.META_08,0)) ELSE 0  END) + " & _
            "(CASE WHEN P.MES >='09' THEN (NVL(PAF.META_09,0)) ELSE 0  END) +  (CASE WHEN P.MES >='10' THEN (NVL(PAF.META_10,0)) ELSE 0  END) + " & _
            "(CASE WHEN P.MES >='11' THEN (NVL(PAF.META_11,0)) ELSE 0  END) + (CASE WHEN P.MES >='12' THEN (NVL(PAF.META_12,0)) ELSE 0  END)  AS Acumulada, " & _
            "(CASE WHEN PFP.META_MENSUAL1=0 THEN TO_NUMBER('')  ELSE PFP.META_MENSUAL1  END) AS Visita1, (CASE WHEN PFP.META_MENSUAL2=0 THEN TO_NUMBER('')  ELSE PFP.META_MENSUAL2  END) AS Visita2, " & _
            "(CASE WHEN PFP.META_MENSUAL3=0 THEN TO_NUMBER('')  ELSE PFP.META_MENSUAL3  END) AS Visita3, (CASE WHEN PFP.META_MENSUAL4=0 THEN TO_NUMBER('')  ELSE PFP.META_MENSUAL4  END) AS Visita4 " & _
            "FROM POA_ACTIVIDAD_FINANCIERA PAF " & _
            "INNER JOIN ESPECIFICA_GASTO EG ON EG.ESPECIFICA_GASTO = PAF.ESPECIFICA_GASTO " & _
            "INNER JOIN GENERICA_GASTOS GG ON GG.GENERICA_GASTOS = EG.GENERICA_GASTO " & _
            "INNER JOIN POA_FINANCIERO_PERSONA PFP ON PFP.ID_POA_ACTIVIDAD_FINANCIERA = PAF.ID_POA_ACTIVIDAD_FINANCIERA " & _
             "INNER JOIN PLAN_TRABAJO_POA PTO ON PTO.ID_PLAN_TRABAJO_POA = PFP.ID_PLAN_TRABAJO_POA  " & _
            "INNER JOIN PLAN_TRABAJO_PERSONA PTE ON PTE.ID_PLAN_TRABAJO_PERSONA = PTO.ID_PLAN_TRABAJO_PERSONA  " & _
            "INNER JOIN PERIODO P ON P.PERIODO = PTE.PERIODO  " & _
            "WHERE PAF.POA = '" & strPOA & "'"
            If strPeriodo <> "all" Then
                lxc_SQL = lxc_SQL & "AND P.PERIODO = '" & strPeriodo & "'"
            End If
            lxc_SQL = lxc_SQL + " ORDER BY PFP.ID_POA_ACTIVIDAD_FINANCIERA "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

End Class
